package foundation.SurModel;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import abstractGA.result.AbsGADAO;
import abstractGA.result.ChangeElem;
import abstractGA.result.ChromOut;
import abstractGA.result.OneOpt;
import foundation.dbUtil.DBException;

public class SMDAO extends AbsGADAO {

	public SMDAO(String cfgFileName) {
		super(cfgFileName);
	}

	public SMDAO() {
		super();
	}

	/**
	 * 
	 * @param oneOpti:将一次优化后的信息存入数据库中
	 * @return
	 */

	public boolean addPefIdx(PefIdx pefIdx) {
		boolean rlt = true;
		try {
			// 向表T_idx中增加记录
			String sqlTxt = "insert into t_idx(runID,genID,optID,paraTypeNm,elemName,paraValue)";
			sqlTxt += "values(?,?,?,?,?,?)";// 共6个参数
			// 产生一个预编译的命令对象
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			// 获取参数值
			String runID = pefIdx.getRunID();// 算法运行标识
			String genID = pefIdx.getGenID();// 演化代数
			String optID = pefIdx.getOptID();// 优化次数
			String paraTypeNm = pefIdx.getParaTypeNm();// 参数类型
			String elemName = pefIdx.getElemName();// 元素名称
			float paraValue = pefIdx.getParaValue();// 值

			// 设定参数值
			pStmt.setString(1, runID);
			pStmt.setString(2, genID);
			pStmt.setString(3, optID);
			pStmt.setString(4, paraTypeNm);
			pStmt.setString(5, elemName);
			pStmt.setFloat(6, paraValue);

			// 增加主记录
			pStmt.executeUpdate();
			pStmt.close();

		} catch (DBException | SQLException e) {
			rlt = false;
			e.printStackTrace();
		}

		return rlt;
	}

	//////////// 李松完成
	/**
	 * 获取一组最优染色体数据（按响应时间从小到大排序，取前面最好的）
	 * 
	 * @param runID:算法某次运行的标识
	 * @param frmNum:按运行时间排序后开始取的记录（从0开始）
	 * @param count:
	 *            取多少个染色体数据
	 * @return:一组最好的染色体数据集
	 */
	public List<ChromOut> getBestChroms(String runID, String genID, int count) {
		List<ChromOut> bestChromOuts = new ArrayList<ChromOut>();
		String sqlTxt = "SELECT Code,RestValue,ReltValue,costValue FROM t_run " + "where runID =  '" + runID
				+ "' and genID =  '" + genID + "' " + "ORDER BY RestValue ASC;";
		try {
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			ResultSet rs = pStmt.executeQuery();
			int i = 0;
			while (rs.next() && i < count) {
				ChromOut bestChromOut = new ChromOut();
				// bestChromOut.setGenID(rs.getString("genID"));
				bestChromOut.setCodes(rs.getString("code"));
				bestChromOut.setObjVal1(rs.getFloat("RestValue"));
				bestChromOut.setObjVal2(rs.getFloat("ReltValue"));
				bestChromOut.setObjVal3(rs.getFloat("costValue"));
				bestChromOuts.add(bestChromOut);
				i++;
			}
			rs.close();
			pStmt.close();
		} catch (DBException | SQLException e) {
			e.printStackTrace();
		}
		return bestChromOuts;
	}

	public boolean isExisted(List<StringBuffer> codes) {
		boolean rlt = false;
		// 将编码转变成字符串，这部分代码后面要重构
		String codeStr = "[";
		for (int i = 0; i < 22; i++) {
			String temp=codes.get(i).toString().trim();
			if (i == 0) {
				codeStr +=temp ;
			} else {
				codeStr += ", " + temp;
			}
		}
		codeStr += "]";

		String sqlTxt = "SELECT count(*) count FROM t_run where code =?";
		try {
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			// 设定参数值
			pStmt.setString(1, codeStr);
			// 查询记录
			ResultSet rs = pStmt.executeQuery();
			while (rs.next()) {
				if (rs.getInt("count") > 0) {
					rlt = true;
				}
				break;
			}
			rs.close();
			pStmt.close();
		} catch (DBException |

		SQLException e)

		{
			e.printStackTrace();
		}
		return rlt;

	}

	/**
	 * 获取离当前时间最近的一组染色体数据（按运行时间排序，最近的排最前）
	 * 
	 * @param runID:算法某次运行的标识
	 * @param frmNum:按运行时间排序后开始取的记录（从0开始）
	 * @param count:
	 *            取多少个染色体数据
	 * @return:染色体数据集
	 */
	public List<ChromOut> getLatestChroms(String runID, int frmNum, int count,String sql) {
		List<ChromOut> bestChromOuts = new ArrayList<ChromOut>();
//		String sqlTxt = "SELECT optID,code,RestValue  FROM t_run  WHERE runID=?";
//		sqlTxt += " ORDER BY optid+0 DESC LIMIT ?,?";

		// 产生一个预编译的命令对象
		try {
			PreparedStatement pStmt = dbUtil.getPrepStmt(sql);
			// 设定参数值
			pStmt.setString(1, runID);
//			pStmt.setInt(2, frmNum);
//			pStmt.setInt(3, count);
			ResultSet rs = pStmt.executeQuery();
			while (rs.next()) {
				ChromOut bestChromOut = new ChromOut();
				bestChromOut.setGenID(rs.getString("optID"));

				bestChromOut.setCodes(rs.getString("code"));
				bestChromOut.setObjVal1(rs.getFloat("RestValue"));
				// bestChromOut.setObjVal2(rs.getFloat("ReltValue"));
				// bestChromOut.setChgedElems(rs.getString("chgedElems"));
				// bestChromOut.setBest(true);
				System.out.println("optID=" + rs.getString("optID") + ",rest=" + rs.getFloat("RestValue"));
				bestChromOuts.add(bestChromOut);
			}
			rs.close();
			pStmt.close();
		} catch (DBException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(sql);
		return bestChromOuts;
	}
	
	public List<ChromOut> getTestChroms(String runID, int frmNum, int count) {
		List<ChromOut> bestChromOuts = new ArrayList<ChromOut>();
		String sqlTxt = "SELECT optID,code,cpuIDMaxUtil  FROM t_temp_run  WHERE runID=?";
//		sqlTxt += " ORDER BY optid+0 DESC LIMIT ?,?";

		// 产生一个预编译的命令对象
		try {
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			// 设定参数值
			pStmt.setString(1, runID);
//			pStmt.setInt(2, frmNum);
//			pStmt.setInt(3, count);
			ResultSet rs = pStmt.executeQuery();
			while (rs.next()) {
				ChromOut bestChromOut = new ChromOut();
				bestChromOut.setGenID(rs.getString("optID"));

				bestChromOut.setCodes(rs.getString("code"));
				bestChromOut.setObjVal1(rs.getFloat("cpuIDMaxUtil"));
				// bestChromOut.setObjVal2(rs.getFloat("ReltValue"));
				// bestChromOut.setChgedElems(rs.getString("chgedElems"));
				// bestChromOut.setBest(true);
				System.out.println("optID=" + rs.getString("optID") + ",rest=" + rs.getFloat("RestValue"));
				bestChromOuts.add(bestChromOut);
			}
			rs.close();
			pStmt.close();
		} catch (DBException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(sqlTxt);
		return bestChromOuts;
	}

	public boolean addAlgRun2(OneOpt oneOpti) {
		boolean rlt = true;
		try {
			// 向主表T_Run中增加记录
			String sqlTxt = "insert into t_run(runID,genID,optID,runTime,Code,RestValue,ReltValue,costValue)";
			sqlTxt += "values(?,?,?,?,?,?,?,?)";// 共8个参数
			// 产生一个预编译的命令对象
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			// 获取参数值
			String runID = oneOpti.getRunID();// 算法运行标识
			String genID = oneOpti.getGenID();// 演化代数
			String optID = oneOpti.getOptID();// 优化次数
			String runTime = oneOpti.getRunTime();// 运行时间
			String code = oneOpti.getcode();// 个体编码
			float restValue = oneOpti.getRestValue();// 响应时间值
			float reltValue = oneOpti.getReltValue();// 可靠性值
			float costValue = oneOpti.getCostValue();// 可靠性值
			// 设定参数值
			pStmt.setString(1, runID);
			pStmt.setString(2, genID);
			pStmt.setString(3, optID);
			pStmt.setString(4, runTime);
			pStmt.setString(5, code);
			pStmt.setFloat(6, restValue);
			pStmt.setFloat(7, reltValue);
			pStmt.setFloat(8, costValue);
			// 增加主记录
			pStmt.executeUpdate();
			pStmt.close();

			// 向子表T_Change中增加多条记录
/*			ArrayList<ChangeElem> chgElems = oneOpti.getChgElems();

			if (chgElems.size() > 0) {
				sqlTxt = "insert into t_change (runID,genID,optID,DoFID,elemName,value) ";
				sqlTxt += "values(?,?,?,?,?,?)";// 共6个参数
				pStmt = dbUtil.getPrepStmt(sqlTxt);// 构成批命令一次提交给数据库执行
				for (ChangeElem changeElem : chgElems) {
					// 获取参数值
					String DoFID = changeElem.getDoFID();// 自由度ID
					String elemName = changeElem.getElemName();// 变更元素的名称
					String value = changeElem.getValue();
					// 设定参数值
					pStmt.setString(1, runID);
					pStmt.setString(2, genID);
					pStmt.setString(3, optID);
					pStmt.setString(4, DoFID);
					pStmt.setString(5, elemName);
					pStmt.setString(6, value);
					// 增加批量参数
					pStmt.addBatch();
				}
				int counts[] = pStmt.executeBatch();// 执行批命令
				if (counts.length == 0) {// 更新数据库的记录等于0条
					rlt = false;
				}
			}*/

			pStmt.close();
			// dbUtil.closeConn(); //避免频繁构造和销毁数据库连接
		} catch (SQLException e) {
			rlt = false;
			e.printStackTrace();

		} catch (DBException e) {
			rlt = false;
			e.printStackTrace();
		}

		return rlt;
	}
	
	public boolean addAlgRun3(OneOpt oneOpti) {
		boolean rlt = true;
		try {
			// 向主表T_Run中增加记录
			String sqlTxt = "insert into t_temp_run(runID,genID,optID,runTime,Code,RestValue,ReltValue,costValue)";
			sqlTxt += "values(?,?,?,?,?,?,?,?)";// 共8个参数
			// 产生一个预编译的命令对象
			PreparedStatement pStmt = dbUtil.getPrepStmt(sqlTxt);
			// 获取参数值
			String runID = oneOpti.getRunID();// 算法运行标识
			String genID = oneOpti.getGenID();// 演化代数
			String optID = oneOpti.getOptID();// 优化次数
			String runTime = oneOpti.getRunTime();// 运行时间
			String code = oneOpti.getcode();// 个体编码
			float restValue = oneOpti.getRestValue();// 响应时间值
			float reltValue = oneOpti.getReltValue();// 可靠性值
			float costValue = oneOpti.getCostValue();// 可靠性值
//			float cpuIDMaxUtil = oneOpti.getCpuIDMaxUtil();
			// 设定参数值
			pStmt.setString(1, runID);
			pStmt.setString(2, genID);
			pStmt.setString(3, optID);
			pStmt.setString(4, runTime);
			pStmt.setString(5, code);
			pStmt.setFloat(6, restValue);
			pStmt.setFloat(7, reltValue);
			pStmt.setFloat(8, costValue);
//			pStmt.setFloat(9, cpuIDMaxUtil);
			// 增加主记录
			pStmt.executeUpdate();
			pStmt.close();

			// 向子表T_Change中增加多条记录
/*			ArrayList<ChangeElem> chgElems = oneOpti.getChgElems();

			if (chgElems.size() > 0) {
				sqlTxt = "insert into t_change (runID,genID,optID,DoFID,elemName,value) ";
				sqlTxt += "values(?,?,?,?,?,?)";// 共6个参数
				pStmt = dbUtil.getPrepStmt(sqlTxt);// 构成批命令一次提交给数据库执行
				for (ChangeElem changeElem : chgElems) {
					// 获取参数值
					String DoFID = changeElem.getDoFID();// 自由度ID
					String elemName = changeElem.getElemName();// 变更元素的名称
					String value = changeElem.getValue();
					// 设定参数值
					pStmt.setString(1, runID);
					pStmt.setString(2, genID);
					pStmt.setString(3, optID);
					pStmt.setString(4, DoFID);
					pStmt.setString(5, elemName);
					pStmt.setString(6, value);
					// 增加批量参数
					pStmt.addBatch();
				}
				int counts[] = pStmt.executeBatch();// 执行批命令
				if (counts.length == 0) {// 更新数据库的记录等于0条
					rlt = false;
				}
			}*/

			pStmt.close();
			// dbUtil.closeConn(); //避免频繁构造和销毁数据库连接
		} catch (SQLException e) {
			rlt = false;
			e.printStackTrace();

		} catch (DBException e) {
			rlt = false;
			e.printStackTrace();
		}

		return rlt;
	}
	
	public static void main(String[] args){
		SMDAO dao = new SMDAO();
//		dao.getLatestChroms("!!SMDFGA", 400, 400);
	}

}
